
--region Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[Players_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[Players_Insert]

IF OBJECT_ID(N'[dbo].[Players_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[Players_Update]

IF OBJECT_ID(N'[dbo].[Players_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[Players_Delete]

IF OBJECT_ID(N'[dbo].[Players_Select]') IS NOT NULL
	DROP PROCEDURE [dbo].[Players_Select]

--endregion

GO


--region [dbo].[Players_Select]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Players_Select]
	@Page int = null,
	@PageSize int = null,
    @SortBy nvarchar(255) = null,
	@SortType bit = null,
	@IdPlayer int = null,
	@PlayerName nvarchar(100) = null,
	@IdCountry int = null,
	@Birthdate datetime = null,
	@BirthPlace nvarchar(100) = null,
	@Height nvarchar(100) = null,
	@Weight nvarchar(100) = null,
	@Position nvarchar(100) = null,
	@PlayerType int = null,
	@Avatar nvarchar(100) = null
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Declare @RowCount int  
Set @RowCount = (
					Select Count (*) 
					From [dbo].[Players]
					WHERE
					(
						(@IdPlayer is null OR [Players].[IdPlayer] = @IdPlayer)
						AND (@PlayerName is null OR [Players].[PlayerName] like @PlayerName)
						AND (@IdCountry is null OR [Players].[IdCountry] = @IdCountry)
						AND (@Birthdate is null OR [Players].[Birthdate] = @Birthdate)
						AND (@BirthPlace is null OR [Players].[BirthPlace] like @BirthPlace)
						AND (@Height is null OR [Players].[Height] like @Height)
						AND (@Weight is null OR [Players].[Weight] like @Weight)
						AND (@Position is null OR [Players].[Position] like @Position)
						AND (@PlayerType is null OR [Players].[PlayerType] = @PlayerType)
						AND (@Avatar is null OR [Players].[Avatar] like @Avatar)
					)
				)
if(@Page is null)
begin
	Set @Page = 1
end

if(@PageSize is null)
begin
	Set @PageSize = @RowCount
end

if(@SortBy is null)
begin
	Set @SortBy = 'DBNull'
end

if(@SortType is null)
begin
	Set @SortType = 1
end

SELECT *
FROM   (
			SELECT [dbo].[Players].*,
                   ROW_NUMBER() OVER 
                   (ORDER BY
                        CASE WHEN @SortBy = 'DBNull' THEN NewID() END ASC,
						CASE WHEN @SortBy = 'IdPlayer' and @SortType = 1 THEN [Players].[IdPlayer] END ASC,
						CASE WHEN @SortBy = 'IdPlayer' and @SortType = 0 THEN [Players].[IdPlayer] END DESC,
						CASE WHEN @SortBy = 'PlayerName' and @SortType = 1 THEN [Players].[PlayerName] END ASC,
						CASE WHEN @SortBy = 'PlayerName' and @SortType = 0 THEN [Players].[PlayerName] END DESC,
						CASE WHEN @SortBy = 'IdCountry' and @SortType = 1 THEN [Players].[IdCountry] END ASC,
						CASE WHEN @SortBy = 'IdCountry' and @SortType = 0 THEN [Players].[IdCountry] END DESC,
						CASE WHEN @SortBy = 'Birthdate' and @SortType = 1 THEN [Players].[Birthdate] END ASC,
						CASE WHEN @SortBy = 'Birthdate' and @SortType = 0 THEN [Players].[Birthdate] END DESC,
						CASE WHEN @SortBy = 'BirthPlace' and @SortType = 1 THEN [Players].[BirthPlace] END ASC,
						CASE WHEN @SortBy = 'BirthPlace' and @SortType = 0 THEN [Players].[BirthPlace] END DESC,
						CASE WHEN @SortBy = 'Height' and @SortType = 1 THEN [Players].[Height] END ASC,
						CASE WHEN @SortBy = 'Height' and @SortType = 0 THEN [Players].[Height] END DESC,
						CASE WHEN @SortBy = 'Weight' and @SortType = 1 THEN [Players].[Weight] END ASC,
						CASE WHEN @SortBy = 'Weight' and @SortType = 0 THEN [Players].[Weight] END DESC,
						CASE WHEN @SortBy = 'Position' and @SortType = 1 THEN [Players].[Position] END ASC,
						CASE WHEN @SortBy = 'Position' and @SortType = 0 THEN [Players].[Position] END DESC,
						CASE WHEN @SortBy = 'PlayerType' and @SortType = 1 THEN [Players].[PlayerType] END ASC,
						CASE WHEN @SortBy = 'PlayerType' and @SortType = 0 THEN [Players].[PlayerType] END DESC,
						CASE WHEN @SortBy = 'Avatar' and @SortType = 1 THEN [Players].[Avatar] END ASC,
						CASE WHEN @SortBy = 'Avatar' and @SortType = 0 THEN [Players].[Avatar] END DESC                   
                    ) AS RowNumber 
			FROM [dbo].[Players]
			Where 
			(
				(@IdPlayer is null OR [Players].[IdPlayer] = @IdPlayer)
				AND (@PlayerName is null OR [Players].[PlayerName] like @PlayerName)
				AND (@IdCountry is null OR [Players].[IdCountry] = @IdCountry)
				AND (@Birthdate is null OR [Players].[Birthdate] = @Birthdate)
				AND (@BirthPlace is null OR [Players].[BirthPlace] like @BirthPlace)
				AND (@Height is null OR [Players].[Height] like @Height)
				AND (@Weight is null OR [Players].[Weight] like @Weight)
				AND (@Position is null OR [Players].[Position] like @Position)
				AND (@PlayerType is null OR [Players].[PlayerType] = @PlayerType)
				AND (@Avatar is null OR [Players].[Avatar] like @Avatar)
			)
		) AS Temp
WHERE  RowNumber Between (@Page * @PageSize - @PageSize + 1) And @Page * @PageSize
Return @RowCount


--end [dbo].[Players_Select]
--endregion

GO
--=========================================================================================--

	

--region [dbo].[Players_Insert]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Players_Insert]
	@IdPlayer int OUTPUT,
	@PlayerName nvarchar(100),
	@IdCountry int,
	@Birthdate datetime,
	@BirthPlace nvarchar(100),
	@Height nvarchar(100),
	@Weight nvarchar(100),
	@Position nvarchar(100),
	@PlayerType int,
	@Avatar nvarchar(100)

AS


INSERT INTO [dbo].[Players] 
(
	[PlayerName],
	[IdCountry],
	[Birthdate],
	[BirthPlace],
	[Height],
	[Weight],
	[Position],
	[PlayerType],
	[Avatar]
)
VALUES 
(
	@PlayerName,
	@IdCountry,
	@Birthdate,
	@BirthPlace,
	@Height,
	@Weight,
	@Position,
	@PlayerType,
	@Avatar
)

SET @IdPlayer = SCOPE_IDENTITY()

--end [dbo].[Players_Insert]
--endregion

GO
--=========================================================================================--

--region [dbo].[Players_Update]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Players_Update]
	@IdPlayer int,
	@PlayerName nvarchar(100),
	@IdCountry int,
	@Birthdate datetime,
	@BirthPlace nvarchar(100),
	@Height nvarchar(100),
	@Weight nvarchar(100),
	@Position nvarchar(100),
	@PlayerType int,
	@Avatar nvarchar(100)
AS


UPDATE [dbo].[Players] SET
	[PlayerName] = @PlayerName,
	[IdCountry] = @IdCountry,
	[Birthdate] = @Birthdate,
	[BirthPlace] = @BirthPlace,
	[Height] = @Height,
	[Weight] = @Weight,
	[Position] = @Position,
	[PlayerType] = @PlayerType,
	[Avatar] = @Avatar
WHERE
	[IdPlayer] = @IdPlayer

--end [dbo].[Players_Update]
--endregion

GO
--=========================================================================================--

--region [dbo].[Players_Delete]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Players_Delete]
	@IdPlayer int
AS


DELETE FROM [dbo].[Players]
WHERE
(
	[IdPlayer] = @IdPlayer
)

--end [dbo].[Players_Delete]
--endregion

GO
--=========================================================================================--

